#include "syncremotedb.h"
#include "QSqlQuery"
#include "QSqlError"
#include <QSqlRecord>
#include <QVariant>

#define DBConToRemoteDBName  "DBConToRemoteDB"
#define DBConToLocalDBName "DBConToLocalHost"

SyncRemoteDB::SyncRemoteDB(QObject *parent) :
    QObject(parent)
{

}

void SyncRemoteDB::slotSyncDB(QString remoteHost, QString remoteDBName, QString remoteDBType, QString remoteuserName, QString remotePwd,
      QString localDBName, QString localDBType, QString localUserName, QString localPwd, QStringList listTableNames)
{
    QSqlDatabase connectionToRemoteServer=QSqlDatabase::addDatabase("Q"+remoteDBType.toUpper(),DBConToRemoteDBName);
    QSqlDatabase connectionToLocalServer=QSqlDatabase::addDatabase("Q"+localDBType.toUpper(),DBConToLocalDBName);

    connectionToRemoteServer.setHostName(remoteHost);
    if(localDBType.toUpper()!="SQLITE")
        connectionToLocalServer.setHostName("127.0.0.1");//数据库所在主机的IP

    connectionToRemoteServer.setDatabaseName(remoteDBName);
    connectionToLocalServer.setDatabaseName(localDBName);

    if(!connectionToRemoteServer.open(remoteuserName,remotePwd))
    {
        emit sigInfo("Can't connect to database in remote server\n"
                     +connectionToRemoteServer.lastError().text()+"\nSyncronization aborted.");
        return;
    }
    else
    {
        queryRemoteServer=new QSqlQuery(QSqlDatabase::database(DBConToRemoteDBName));
         queryRemoteServer->setForwardOnly(true);
         emit sigInfo("INFO: Connect to database in remote server successfully");
    }

    if(!localUserName.isEmpty())
    {
        if(!connectionToLocalServer.open(localUserName,localPwd))
        {
            emit sigInfo("Can't connect to database in local server\n"+connectionToLocalServer.lastError().text()
                    +"\nSyncronization aborted.");
            return;
        }
        else
        {
            queryLocalServer=new QSqlQuery(QSqlDatabase::database(DBConToLocalDBName));
             queryRemoteServer->setForwardOnly(true);
             emit sigInfo("INFO: Connect to database in local server successfully");
        }
    }
    else
    {
        if(!connectionToLocalServer.open())
        {
            emit sigInfo("Can't connect to database in local server\n"+connectionToLocalServer.lastError().text()
                         +"\nSyncronization aborted.");
            return;
        }
        else
        {
            queryLocalServer=new QSqlQuery(QSqlDatabase::database(DBConToLocalDBName));
             queryRemoteServer->setForwardOnly(true);
             emit sigInfo("INFO: Connect to database in local server successfully");
        }
    }

    syncDBWithRemoteServer(listTableNames);


    delete queryRemoteServer;
    delete queryLocalServer;
}

void SyncRemoteDB::syncDBWithRemoteServer(QStringList listTableNames)
{

    qint16 tableNum=listTableNames.size();
    for(int indexOfTable=0;indexOfTable<tableNum;indexOfTable++)
    {
        emit sigInfo("Syncronizing table "+listTableNames.at(indexOfTable));
        if(!queryRemoteServer->exec("select * from "+listTableNames.at(indexOfTable)))
        {
            emit sigInfo("Can't query data from "+listTableNames.at(indexOfTable)+"\nSyncronization aborted.");
            return;
        }
        else
        {
            if(!queryLocalServer->exec("delete from "+listTableNames.at(indexOfTable)))
            {
                emit sigInfo("Can't delete from "+listTableNames.at(indexOfTable)+"\nSyncronization aborted.");
                return;
            }
            else
            {
                QString strPlaceHolder;

                qint16 columnCount=queryRemoteServer->record().count();
                qint64 rowCount=queryRemoteServer->size();

                if (columnCount<=0)
                    return;
                else
                {
                    for(int n=0;n<columnCount-1;n++)
                    {
                        strPlaceHolder.append("?,");
                    }
                    strPlaceHolder.append('?');
                }

                for(qint64 indOfRow=0;indOfRow<rowCount;indOfRow++)
                {
                    queryRemoteServer->next();
                    queryLocalServer->prepare("INSERT INTO "+listTableNames.at(indexOfTable)+
                                             " VALUES("+strPlaceHolder+")");
                    for(qint16 indOfCol=0;indOfCol<columnCount;indOfCol++)
                    {
                        queryLocalServer->bindValue(indOfCol, queryRemoteServer->value(indOfCol));
                    }
                    queryLocalServer->exec();

                }
                emit sigInfo("Table "+listTableNames.at(indexOfTable)+" syncronization complete.\n"
                             +QString::number(rowCount)+" rows in total.");


            }
        }
    }
    emit sigInfo("-----------------------------------\n"
                 "Congratulations! Syncronization is complete!");
}

SyncRemoteDB::~SyncRemoteDB()
{

}
